from PySide6.QtWidgets import QWidget,QAbstractItemView,QMenu,QTableWidgetItem,QMessageBox
from PySide6.QtCore import Qt
import pandas as pd
from sqlalchemy.orm import Session
from settings.publicvar import engine
from settings.publicFuncation.exceldownloadimport import OperationModel
from views.bankinfo.bankinfo.bankinfo import Ui_item

from views.bankinfo.model.bankinfo import BankInfo
from views.bankinfo.bankinfo.operationView import OperationBankInfoView



class BankAccountView(QWidget,Ui_item):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.itemtable.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.itemtable.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.itemtable.setContextMenuPolicy(Qt.CustomContextMenu)
        self.itemtable.customContextMenuRequested.connect(self.generate_menu)

        self.loadTableData()
        self.addbtn.clicked.connect(self.addFun)
        self.sereachedit.editingFinished.connect(self.serearchFun)
        self.serearchbtn.clicked.connect(self.serearchFun)
        self.downloadmodle.clicked.connect(self.downloadmodleFun)
        self.importbtn.clicked.connect(self.importdataFun)
        self.autoLoadData()


        # 读取所有数据
    def loadTableData(self):
        with Session(engine) as session, session.begin():
            items = session.query(BankInfo).all()
            self.setTableStructure(items)

    def autoLoadData(self):
        items_names = ['idx', 'bankname', 'name', 'IDNumber', 'cardnumber', 'openbank', 'pupose']
        dtypes = {'IDNumber': str, 'cardnumber': str}
        url = 'state/model/银行账户信息默认数据.xlsx'
        try:
            data = pd.read_excel(url, sheet_name=0, header=1,
                                 names=items_names,
                                 dtype=dtypes
                                 ).drop('idx', axis=1)
            with Session(engine) as session, session.begin():
                exit_card = session.query(BankInfo).filter(BankInfo.cardnumber.in_(data.cardnumber.to_list())).all()
                not_exit_card = [item.cardnumber for item in exit_card]
                save_card = data[~data.cardnumber.isin(not_exit_card)].to_dict('records')
                items = [BankInfo(**item) for item in save_card]
                session.add_all(items)
        except:
            QMessageBox.information(None,'失败','数据加载失败')
        self.loadTableData()

    # 表格加载数据
    def setTableStructure(self, data):
        self.itemtable.setRowCount(len(data))
        for i, item in enumerate(data):
            idx = QTableWidgetItem(str(item.id))
            self.itemtable.setItem(i, 0, idx)
            bankname = QTableWidgetItem(item.bankname)
            self.itemtable.setItem(i, 1, bankname)
            name = QTableWidgetItem(item.name)
            self.itemtable.setItem(i, 2, name)
            IDNumber = QTableWidgetItem(item.IDNumber)
            self.itemtable.setItem(i,3, IDNumber)
            cardnumber = QTableWidgetItem(item.cardnumber)
            self.itemtable.setItem(i, 4, cardnumber)
            openbank = QTableWidgetItem(item.openbank)
            self.itemtable.setItem(i, 5, openbank)
            pupose = QTableWidgetItem(item.pupose)
            self.itemtable.setItem(i, 6, pupose)


    def addFun(self):
        add = OperationBankInfoView('add',False)
        add.exec()
        self.loadTableData()

    # 获取表单
    def generate_menu(self,pos):
        menu = QMenu()
        viewitem = menu.addAction('查看')
        changeitem = menu.addAction('修改')
        deleteitm = menu.addAction('删除')
        action = menu.exec(self.itemtable.mapToGlobal(pos))
        posrow = self.itemtable.currentRow()
        columns = self.itemtable.columnCount()
        data = [self.itemtable.item(posrow, i).text() for i in range(columns)]
        for i in range(columns):
            item = self.itemtable.item(posrow,i)
            if item is None:
                data.append('')
            else:
                data.append(item.text())
        if action == viewitem:
            view = OperationBankInfoView('view', data)
            view.exec()
        elif action == changeitem:
            change = OperationBankInfoView('change', data)
            change.exec()
        elif action == deleteitm:
            delete = OperationBankInfoView('delete',data)
            delete.exec()
        self.loadTableData()

    # 搜索框数据处理
    def serearchFun(self):
        sereachdata = self.sereachedit.text().strip()
        if sereachdata == '':
            QMessageBox.critical(None, '错误', '搜索框不能为空！')
            self.loadTableData()
        else:
            with Session(engine) as session, session.begin():
                sereachbankname = session.query(BankInfo).filter(
                    BankInfo.bankname.like('%' + sereachdata + '%')).all()
                sereachname = session.query(BankInfo).filter(
                    BankInfo.name.like('%' + sereachdata + '%')).all()
                sereachIDNumber = session.query(BankInfo).filter(
                    BankInfo.IDNumber.like('%' + sereachdata + '%')).all()
                sereachcardnumber = session.query(BankInfo).filter(
                    BankInfo.cardnumber.like('%' + sereachdata + '%')).all()
                sereachopenbank = session.query(BankInfo).filter(
                    BankInfo.openbank.like('%' + sereachdata + '%')).all()
                sereachData = sereachbankname + sereachname + sereachIDNumber + sereachcardnumber + sereachopenbank
                items = []
                if sereachData:
                    for item in sereachData:
                        items.append(item)
                else:
                    QMessageBox.critical(None, '没有数据', '你搜索数据不存在！')
                self.setTableStructure(items)

    def downloadmodleFun(self):
        OperationModel().downloadmodleFun('银行账户信息模板.xlsx')

    def importdataFun(self):
        items_names = ['idx','bankname','name','IDNumber','cardnumber','openbank','pupose']
        dtypes = {'IDNumber':str,'cardnumber':str}
        data = OperationModel().importdataFun(items_names,dtypes)
        with Session(engine) as session, session.begin():
            exit_card = session.query(BankInfo).filter(BankInfo.cardnumber.in_(data.cardnumber.to_list())).all()
            not_exit_card = [item.cardnumber for item in exit_card]
            save_card = data[~data.cardnumber.isin(not_exit_card)].to_dict('records')
            items = [BankInfo(**item) for item in save_card]
            session.add_all(items)
            QMessageBox.information(None, '提示',
                                    '你导入成功%s条，失败%s条，明细如下！' % (len(items), len(data) - len(items)))
        self.loadTableData()